﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using Dapper;
using SqlBuilder.Model;

namespace SqlBuilder.Code
{
    public class SqlBuilderDAL
    {
        /// <summary>
        /// 查询模板
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public List<MSQLTemplate> QuerySQLTemplate(IDbConnection conn, string name)
        {
            string sql = "select * from sqltemplate ";
            if (!string.IsNullOrEmpty(name))
            {
                sql += string.Format("where name like '%{0}%'", name);
            }

            return conn.Query<MSQLTemplate>(sql).ToList();
        }

        /// <summary>
        /// 查询模板
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public MSQLTemplate QuerySQLTemplateById(IDbConnection conn, string id)
        {
            string sql = "select * from sqltemplate where id=@id";
            return conn.QueryFirstOrDefault<MSQLTemplate>(sql, new { id = id });
        }

        /// <summary>
        /// 更新模板
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="trans"></param>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool UpdateSQLTemplate(IDbConnection conn, IDbTransaction trans, MSQLTemplate model)
        {
            string sql = "update sqltemplate set name=@name,template=@template where id=@id";

            return conn.Execute(sql, model, trans) > 0;
        }

        /// <summary>
        /// 删除模板
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool DeleteSQLTemplate(IDbConnection conn,IDbTransaction trans, string id)
        {
            string sql = "delete from sqltemplate where id=@id";

            return conn.Execute(sql, new { id = id }, trans) > 0;
        }

        /// <summary>
        /// 添加数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool AddSQLTemplate(IDbConnection conn, IDbTransaction trans, MSQLTemplate model)
        {

            model.id = Guid.NewGuid().ToString();
            string sql = "insert  into sqltemplate (id,name,template) values (@id,@name,@template)";
            return conn.Execute(sql, model, trans) > 0;
        }

        /// <summary>
        /// 查询模板映射
        /// </summary>
        /// <param name="templateid"></param>
        /// <returns></returns>
        public List<MSQLMap> QuerySqlMapById(IDbConnection conn, string templateid)
        {
            string sql = "select  * from sqlmap where templateId=@id";
            return conn.Query<MSQLMap>(sql,new { id=templateid}).ToList();
        }

        /// <summary>
        ///删除
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public bool DeleteSqlMaps(IDbConnection conn, List<string> ids)
        {
            string sql = string.Format("delete from sqlmap where id in ('{0}')", string.Join("','", ids));
            return conn.Execute(sql) > 0;
        }

        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="maps"></param>
        /// <returns></returns>
        public bool AddSqlMaps(IDbConnection conn, IDbTransaction trans, List<MSQLMap> maps)
        {

            StringBuilder sb = new StringBuilder();
            sb.Append("insert  into sqlmap (id,templateId,template,excelIndex,script) values ");
            List<string> values = new List<string>();
            foreach (var item in maps)
            {
                values.Add(string.Format("('{0}','{1}','{2}','{3}','{4}')", Guid.NewGuid().ToString(), item.templateId, item.template ?? string.Empty, item.excelIndex ?? string.Empty,item.script??string.Empty));
            }

            sb.Append(string.Join(",", values));

            return conn.Execute(sb.ToString(), null, trans) > 0;
        }


        public bool DeleteSQlMapsByTId(IDbConnection conn, IDbTransaction trans, string tid)
        {
            string sql = "delete from sqlmap where templateId=@templateId";
            return conn.Execute(sql, new { templateId = tid }, trans) > 0;
        }

    }
}
